﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace WinFormStore
{
    class DBHelper
    {
        //连接字符串
        public static string connStr = "server=.;database=WinformStoreDB;uid=sa;pwd=123456";
        //连接对象
        public static SqlConnection conn = null;
        //定义数据适配器
        public static SqlDataAdapter adp = null;

        #region 连接数据库
        public static void OpenConn()
        {
            if (conn == null)
            {
                conn = new SqlConnection(connStr);
                conn.Open();
            }
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            if (conn.State == System.Data.ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }
        #endregion

        #region 执行sql语句前的准备（将sql传入adapter对象）
        public static void PrepareSql(string sql)
        {
            OpenConn();
            adp = new SqlDataAdapter(sql, conn);
        }
        public static void PrepareProc(string sql)
        {
            OpenConn();
            adp = new SqlDataAdapter(sql, conn);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        }
        #endregion

        #region  设置sql语句的参数
        //设置sql语句的参数
        public static void SetParameter(string parameterName, object parameterValue)
        {
            parameterName = "@" + parameterName.Trim();
            if (parameterValue == null)
            {
                parameterValue = DBNull.Value;
            }
            adp.SelectCommand.Parameters.Add(new SqlParameter(parameterName, parameterValue));
        }

        //设置输出参数(非字符串类型)
        public static void SetOutParameter(string parameterName,SqlDbType dbType)
        {
            parameterName = "@" + parameterName.Trim();
            SqlParameter parameter = new SqlParameter(parameterName,dbType);
            parameter.Direction = ParameterDirection.Output;
            adp.SelectCommand.Parameters.Add(parameter);
        }
        //设置输出参数(字符串类型)
        public static void SetOutParameter(string parameterName, SqlDbType dbType,int size)
        {
            parameterName = "@" + parameterName.Trim();
            SqlParameter parameter = new SqlParameter(parameterName, dbType,size);
            parameter.Direction = ParameterDirection.Output;
            parameter.Size = size;
            adp.SelectCommand.Parameters.Add(parameter);
        }
        //设置和获取sql语句的参数
        public static object GetParameter(string parameterName)
        {
            parameterName = "@" + parameterName.Trim();
            return adp.SelectCommand.Parameters[parameterName].Value;
        }

        //设置输入输出参数(非字符串类型)
        public static void SetInOutParameter(string parameterName, SqlDbType dbType,object parameterValue)
        {
            parameterName = "@" + parameterName.Trim();
            SqlParameter parameter = new SqlParameter(parameterName, dbType);
            parameter.Direction = ParameterDirection.InputOutput;
            parameter.Value = parameterValue;
            adp.SelectCommand.Parameters.Add(parameter);
        }
        //设置输入输出参数(字符串类型)
        public static void SetInOutParameter(string parameterName, SqlDbType dbType, int size, object parameterValue)
        {
            parameterName = "@" + parameterName.Trim();
            SqlParameter parameter = new SqlParameter(parameterName, dbType, size);
            parameter.Direction = ParameterDirection.InputOutput;
            parameter.Size = size;
            parameter.Value = parameterValue;
            adp.SelectCommand.Parameters.Add(parameter);
        }
        //设置返回值的参数
        public static void SetReturnParameter(string parameterName)
        {
            parameterName = "@" + parameterName.Trim();
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = parameterName;
            parameter.Direction = ParameterDirection.ReturnValue;
            adp.SelectCommand.Parameters.Add(parameter);
        }
        #endregion

        #region 执行sql语句
        public static int ExecuteNonQuery()
        {
            int count = adp.SelectCommand.ExecuteNonQuery();
            conn.Close();
            return count;
        }
        public static DataTable ExecuteQuery()
        {
            DataTable dt = new DataTable();
            adp.Fill(dt);
            conn.Close();
            return dt;
        }
        public static SqlDataReader ExecuteDataReader()
        {
            return adp.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        public static object ExecuteScalar()
        {
            object obj = adp.SelectCommand.ExecuteScalar();
            conn.Close();
            return obj;
        }
        #endregion
    }
}
